/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package amd.database;

import java.util.ArrayList;

/**
 *
 * @author Latifah Dwi Nurachma
 */
public class DbDocumentSimilarity {

    public static ArrayList<ArrayList<String>> getDocumentSimilar(ConnectDB conn, String idDocReff) throws QueryNoResultException {
//    public static ArrayList<String> getDocumentSimilar(ConnectDB conn, String idDocReff) throws QueryNoResultException {
        String sql = "SELECT id_doc2, similarity from similar where id_doc1 = \'"+idDocReff+"\' and similarity>0  UNION "+
                     "SELECT id_doc1, similarity from similar where id_doc2 = \'"+idDocReff+"\' and similarity>0 order by similarity desc";
        ArrayList<ArrayList<Object>> tmpObject = conn.getAllDatas(sql);
        String [] tmpDoc = new String[tmpObject.get(0).size()];
        int i=0;
        while(i<tmpObject.get(0).size()){
            tmpDoc[i] = tmpObject.get(0).get(i).toString();
            i++;
        }

//        ArrayList<ArrayList<String>> listDoc = new ArrayList<ArrayList<String>>();

        ArrayList<String> doc = new ArrayList<String>();
        ArrayList<ArrayList<String>> listDoc = new ArrayList<ArrayList<String>>();
        i=0;
        while(i<tmpDoc.length){
            sql = "SELECT * FROM document WHERE id_doc=\'"+tmpDoc[i]+"\'";
            ArrayList<ArrayList<Object>> tmp = conn.getAllDatas(sql);
            int j=0;
            while(j<tmp.size()) {
                ArrayList<Object> ob=tmp.get(j);
                for(int k=0; k<ob.size(); k++)
                    doc.add(k, ob.get(k).toString());
                listDoc.add(j,doc);
                j++;
            }
            i++;
        }

        return listDoc;
    }
    
    public static ArrayList<ArrayList<String>> getDocumentSimilar(ConnectDB conn, String idDocReff, double treshold,int offset, int limit) throws QueryNoResultException {
        //System.out.println("---");
        String sql = "SELECT id_doc1, similarity from similar where id_doc2 = \'"+idDocReff+"\' and similarity>0  UNION "+
                     " SELECT id_doc2, similarity from similar where id_doc1 = \'"+idDocReff+"\' and similarity>"+treshold+" order by similarity desc limit "+offset+","+limit+";";
        //System.out.println(sql);
        ArrayList<ArrayList<Object>> tmpObject = conn.getAllDatas(sql);
        String [] tmpDoc = new String[tmpObject.get(0).size()];
        int i=0;
        while(i<tmpObject.get(0).size()){
            tmpDoc[i] = tmpObject.get(0).get(i).toString();
            //System.out.println(tmpDoc[i]);
            i++;
        }

        ArrayList<ArrayList<String>> listDoc = new ArrayList<ArrayList<String>>();

        i=0;
        ArrayList<String> tmp1 = new ArrayList<String>();
        ArrayList<String> tmp2 = new ArrayList<String>();

        while(i<tmpDoc.length){
            sql = "SELECT id_doc, title FROM tbl_document WHERE id_doc=\'"+tmpDoc[i]+"\';";
            ArrayList<ArrayList<Object>> tmp = conn.getAllDatas(sql);

            int j =0;
            while(j<tmp.get(0).size()){
                //System.out.println(tmp.get(0).get(j) + "----------" + tmp.get(1).get(j));
                tmp1.add(tmp.get(0).get(j).toString());
                tmp2.add(tmp.get(1).get(j).toString());
                j++;
            }
            i++;
        }
        listDoc.add(tmp1);
        listDoc.add(tmp2);
        //System.out.println("---");

        return listDoc;
    }


    public static void insert(ConnectDB conn, String idDoc1, String idDoc2, double similarityValue){
        String sql = "INSERT INTO similar(id_doc1, id_doc2, similarity) " +
                "VALUES (\'"+idDoc1+"\', \'"+idDoc2+"\', "+similarityValue+")";
        //System.out.println(sql);
        conn.createStatement(sql);
    }

    public static void update(ConnectDB conn, String idDoc1, String idDoc2, double similarityValue){
        String sql = "UPDATE similar SET similarity =" + similarityValue +
                " WHERE id_doc1 = \'" + idDoc1 +
                "\' AND id_doc2 = \'" + idDoc2 + "\';";

        //System.out.println(sql);
        conn.createStatement(sql);
    }

//    public static void deleteAllDatas(ConnectDB conn){
//        String sql = "DELETE from tbl_document_similarity where1;";
//        conn.createStatement(sql);
//    }

    public static boolean isExist(ConnectDB conn, String idDoc1, String idDoc2) throws QueryNoResultException{
        String sql = "SELECT id_doc1 FROM similar WHERE id_doc1 = \'" + idDoc1 + "\' AND id_doc2 = \'" + idDoc2 + "\'";
        //System.out.println(sql);
        return conn.isExistInDatabase(sql);
    }


}
